"""
股票数据模型定义
"""
from datetime import datetime
from sqlalchemy import (
    Column, Integer, String, Float, Date, DateTime, ForeignKey, Index, UniqueConstraint
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import relationship
from modules.models.base import Base


class SecurityInfo(Base):
    """证券基本信息"""
    __tablename__ = "security_info"

    id = Column(Integer, primary_key=True, autoincrement=True)
    code = Column(String(10), nullable=False, index=True, unique=True, comment="证券代码")
    name = Column(String(100), nullable=False, comment="证券名称")
    type = Column(String(20), nullable=False, index=True, comment="证券类型：股票/指数/ETF等")
    exchange = Column(String(20), nullable=False, index=True, comment="交易所")
    industry = Column(String(50), nullable=True, index=True, comment="所属行业")
    is_active = Column(Integer, default=1, comment="是否有效")
    create_time = Column(DateTime, default=datetime.now, comment="创建时间")
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    
    # 与行情数据的关联
    daily_quotes = relationship("DailyQuote", back_populates="security", cascade="all, delete-orphan")
    
    # 与财务数据的关联 (导入自 financial.py)
    financial_statements = relationship("FinancialStatement", back_populates="security", cascade="all, delete-orphan")
    valuation_indicators = relationship("ValuationIndicator", back_populates="security", cascade="all, delete-orphan")
    financial_ratios = relationship("FinancialRatio", back_populates="security", cascade="all, delete-orphan")
    
    def __repr__(self):
        return f"<SecurityInfo(code='{self.code}', name='{self.name}', type='{self.type}')>"


class DailyQuote(Base):
    """每日股票行情"""
    __tablename__ = "daily_quotes"

    id = Column(Integer, primary_key=True, autoincrement=True)
    security_id = Column(Integer, ForeignKey("security_info.id"), nullable=False)
    trade_date = Column(Date, nullable=False, comment="交易日期")
    open = Column(Float, nullable=True, comment="开盘价")
    high = Column(Float, nullable=True, comment="最高价")
    low = Column(Float, nullable=True, comment="最低价")
    close = Column(Float, nullable=True, comment="收盘价")
    adj_close = Column(Float, nullable=True, comment="复权收盘价")
    volume = Column(Float, nullable=True, comment="成交量")
    amount = Column(Float, nullable=True, comment="成交额")
    turnover = Column(Float, nullable=True, comment="换手率")
    pe_ratio = Column(Float, nullable=True, comment="市盈率")
    pb_ratio = Column(Float, nullable=True, comment="市净率")
    ps_ratio = Column(Float, nullable=True, comment="市销率")
    total_mv = Column(Float, nullable=True, comment="总市值")
    circ_mv = Column(Float, nullable=True, comment="流通市值")
    create_time = Column(DateTime, default=datetime.now, comment="创建时间")
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")

    # 与证券信息的关联
    security = relationship("SecurityInfo", back_populates="daily_quotes")
    # 与技术指标的关联
    technical_indicators = relationship("TechnicalIndicator", back_populates="daily_quote", cascade="all, delete-orphan")

    __table_args__ = (
        UniqueConstraint('security_id', 'trade_date', name='uix_security_date'),
        Index('idx_security_date', 'security_id', 'trade_date'),
    )
    
    def __repr__(self):
        return f"<DailyQuote(security_id={self.security_id}, trade_date='{self.trade_date}', close={self.close})>"


class TechnicalIndicator(Base):
    """技术指标"""
    __tablename__ = "technical_indicators"

    id = Column(Integer, primary_key=True, autoincrement=True)
    daily_quote_id = Column(Integer, ForeignKey("daily_quotes.id"), nullable=False)
    ma5 = Column(Float, nullable=True, comment="5日均线")
    ma10 = Column(Float, nullable=True, comment="10日均线")
    ma20 = Column(Float, nullable=True, comment="20日均线")
    ma30 = Column(Float, nullable=True, comment="30日均线")
    ma60 = Column(Float, nullable=True, comment="60日均线")
    ma120 = Column(Float, nullable=True, comment="120日均线")
    ma250 = Column(Float, nullable=True, comment="250日均线")
    vol5 = Column(Float, nullable=True, comment="5日成交量均线")
    vol10 = Column(Float, nullable=True, comment="10日成交量均线")
    rsi6 = Column(Float, nullable=True, comment="6日RSI")
    rsi12 = Column(Float, nullable=True, comment="12日RSI")
    rsi24 = Column(Float, nullable=True, comment="24日RSI")
    macd = Column(Float, nullable=True, comment="MACD")
    macd_signal = Column(Float, nullable=True, comment="MACD信号线")
    macd_hist = Column(Float, nullable=True, comment="MACD柱状图")
    boll_upper = Column(Float, nullable=True, comment="布林带上轨")
    boll_middle = Column(Float, nullable=True, comment="布林带中轨")
    boll_lower = Column(Float, nullable=True, comment="布林带下轨")
    kd_k = Column(Float, nullable=True, comment="K值")
    kd_d = Column(Float, nullable=True, comment="D值")
    kd_j = Column(Float, nullable=True, comment="J值")
    extended_indicators = Column(JSONB, nullable=True, comment="扩展指标JSON格式")
    create_time = Column(DateTime, default=datetime.now, comment="创建时间")
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")

    # 与日线数据的关联
    daily_quote = relationship("DailyQuote", back_populates="technical_indicators")
    
    __table_args__ = (
        UniqueConstraint('daily_quote_id', name='uix_daily_quote_id'),
        Index('idx_daily_quote_id', 'daily_quote_id'),
    )

    def __repr__(self):
        return f"<TechnicalIndicator(daily_quote_id={self.daily_quote_id})>"


class CollectionTask(Base):
    """数据采集任务记录"""
    __tablename__ = "collection_tasks"

    id = Column(Integer, primary_key=True, autoincrement=True)
    task_id = Column(String(100), nullable=True, index=True, comment="Celery任务ID")
    task_type = Column(String(50), nullable=False, index=True, comment="任务类型")
    security_code = Column(String(10), nullable=True, index=True, comment="证券代码")
    start_date = Column(Date, nullable=True, comment="开始日期")
    end_date = Column(Date, nullable=True, comment="结束日期")
    status = Column(String(20), default="pending", index=True, comment="任务状态：pending/running/success/failed")
    retry_count = Column(Integer, default=0, comment="重试次数")
    error_message = Column(String(500), nullable=True, comment="错误信息")
    result_count = Column(Integer, default=0, comment="结果数量")
    create_time = Column(DateTime, default=datetime.now, comment="创建时间")
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, comment="更新时间")

    def __repr__(self):
        return f"<CollectionTask(task_type='{self.task_type}', security_code='{self.security_code}', status='{self.status}')>"